//
//  Database.m
//  YanMusicPlayer
//
//  Created by 1564335 on 15-6-16.
//  Copyright (c) 2015年 胡岩 All rights reserved.
//

#import "Database.h"

static sqlite3 *dbPoint = nil;

@implementation Database

+ (void)openDataBase
{
    if (dbPoint) {
        return;
    }
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).firstObject;
    path = [path stringByAppendingPathComponent:@"music.rdb"];
    sqlite3_open(path.UTF8String, &dbPoint);
    
}

+ (void)createTable
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"create table if not exists collect(singer_name NSString, singer_id NSString, url NSString, song_name NSString, lrc_id NSString, songId NSString, musicLength NSString)"];
    NSString *sqlCustom = nil;
    if ([[_USERDEFAULT objectForKey:@"first"] isEqualToString:@"first"]) {
        [_USERDEFAULT setObject:@"second" forKey:@"first"];
    } else {
        sqlCustom = [NSString stringWithFormat:@"create table if not exists collect%@(singer_name NSString, singer_id NSString, url NSString, song_name NSString, lrc_id NSString, songId NSString, musicLength NSString)", [_USERDEFAULT objectForKey:@"tableCount"]];
    }
    NSString *sqlDownload = @"create table if not exists download(singer_name NSString, singer_id NSString, url NSString, song_name NSString, lrc_id NSString, songId NSString, musicLength NSString)";
    
    NSString *sqlTableName = @"create table if not exists tableName(tableName NSString, name NSString)";
    sqlite3_exec(dbPoint, sqlCustom.UTF8String, NULL, NULL, NULL);
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
    sqlite3_exec(dbPoint, sqlDownload.UTF8String, NULL, NULL, NULL);
    sqlite3_exec(dbPoint, sqlTableName.UTF8String, NULL, NULL, NULL);
}

+ (void)insertTableName:(NSString *)tableName :(NSString *)name
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"insert into tableName values ('%@', '%@')", tableName, name];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)insertValue:(MusicModel *)model
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"insert into collect values ('%@', '%@', '%@', '%@', '%@', '%@', '%@')", model.singerName, model.singerId, model.url, model.songName, model.lrcId, model.songId, model.musicLength];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)insertCustomValue:(MusicModel *)model :(NSString *)tableName
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"insert into collect%@ values ('%@', '%@', '%@', '%@', '%@', '%@', '%@')", tableName, model.singerName, model.singerId, model.url, model.songName, model.lrcId, model.songId, model.musicLength];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)insertDownloadValue:(MusicModel *)model
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"insert into download values ('%@', '%@', '%@', '%@', '%@', '%@', '%@')", model.singerName, model.singerId, model.url, model.songName, model.lrcId, model.songId, model.musicLength];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)deleteTableName:(NSString *)tableName
{
    NSString *sql = [NSString stringWithFormat:@"delete from tableName where tableName = %@", tableName];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)deleteTable:(NSString *)tableCount
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"drop table collect%@", tableCount];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)deleteValue:(NSString *)songId :(NSString *)name
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"delete from collect%@ where songId = %@", name, songId];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)deleteValue:(NSString *)songId
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"delete from collect where songId = %@", songId];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (void)deleteDownloadValue:(NSString *)songId
{
    [self openDataBase];
    NSString *sql = [NSString stringWithFormat:@"delete from download where songId = %@", songId];
    sqlite3_exec(dbPoint, sql.UTF8String, NULL, NULL, NULL);
}

+ (NSArray *)selectTableName
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select * from tableName";
    int result = sqlite3_prepare_v2(dbPoint, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSMutableArray *array = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *tableName = sqlite3_column_text(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            NameModel *model = [NameModel getModel];
            model.tableName = [NSString stringWithUTF8String:(const char *)tableName];
            model.name = [NSString stringWithUTF8String:(const char *)name];
            [array addObject:model];
        }
        sqlite3_finalize(stmt);
        return array;
    }
    sqlite3_finalize(stmt);
    return [NSMutableArray array];
}

+ (NSArray *)selectDownloadData
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select * from download";
    int result = sqlite3_prepare_v2(dbPoint, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSMutableArray *array = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *singer_name = sqlite3_column_text(stmt, 0);
            const unsigned char *singer_id = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            const unsigned char *song_name = sqlite3_column_text(stmt, 3);
            const unsigned char *lrcId = sqlite3_column_text(stmt, 4);
            const unsigned char *songId = sqlite3_column_text(stmt, 5);
            const unsigned char *musicLength = sqlite3_column_text(stmt, 6);
            
            MusicModel *model = [MusicModel getMusicModel];
            model.singerName = [NSString stringWithUTF8String:(const char *)singer_name];
            model.singerId = [NSString stringWithUTF8String:(const char *)singer_id];
            model.lrcId = [NSString stringWithUTF8String:(const char *)lrcId];
            model.url = [NSString stringWithUTF8String:(const char *)url];
            model.songName = [NSString stringWithUTF8String:(const char *)song_name];
            model.songId = [NSString stringWithUTF8String:(const char *)songId];
            model.musicLength = [NSString stringWithUTF8String:(const char *)musicLength];
            [array addObject:model];
        }
        sqlite3_finalize(stmt);
        return array;
    }
    sqlite3_finalize(stmt);
    return [NSMutableArray array];
}

+ (NSArray *)selectData:(NSString *)number
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sql = [NSString stringWithFormat:@"select * from collect%@", number];
    int result = sqlite3_prepare_v2(dbPoint, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSMutableArray *array = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *singer_name = sqlite3_column_text(stmt, 0);
            const unsigned char *singer_id = sqlite3_column_text(stmt, 1);
            const unsigned char *url = sqlite3_column_text(stmt, 2);
            const unsigned char *song_name = sqlite3_column_text(stmt, 3);
            const unsigned char *lrcId = sqlite3_column_text(stmt, 4);
            const unsigned char *songId = sqlite3_column_text(stmt, 5);
            const unsigned char *musicLength = sqlite3_column_text(stmt, 6);
            
            MusicModel *model = [MusicModel getMusicModel];
            model.singerName = [NSString stringWithUTF8String:(const char *)singer_name];
            model.singerId = [NSString stringWithUTF8String:(const char *)singer_id];
            model.lrcId = [NSString stringWithUTF8String:(const char *)lrcId];
            model.url = [NSString stringWithUTF8String:(const char *)url];
            model.songName = [NSString stringWithUTF8String:(const char *)song_name];
            model.songId = [NSString stringWithUTF8String:(const char *)songId];
            model.musicLength = [NSString stringWithUTF8String:(const char *)musicLength];
            [array addObject:model];
        }
        sqlite3_finalize(stmt);
        return array;
    }
    sqlite3_finalize(stmt);
    return [NSMutableArray array];
}


@end
